package com.central.face.streaming

import java.util.Properties

import com.central.utils.ConfigUtil
import org.apache.spark.sql.SparkSession

object TransData {
  val properties = new Properties()
  properties.put("user", "root")
  properties.put("password", "root")
  val JDBC: String = ConfigUtil.getProperties().getProperty("mysql.url")
  val TABLE_SITE: String = ConfigUtil.getProperties().getProperty("table.site")

  def transFaceData(spark: SparkSession) = {
    val dataFrame = spark.read.jdbc(JDBC, TABLE_SITE, properties)
    dataFrame.createOrReplaceTempView("t_device_site_relation")
    spark.sql(
      """
        SELECT t1.device_bases_id, t1.device_sites_id
 FROM `t_device_site_relation` t1
 	JOIN (
 		SELECT COUNT(1) AS counts, device_sites_id
 		FROM `t_device_site_relation`
 		GROUP BY device_sites_id
 		HAVING counts > 1
 	) t2
 	ON t1.device_sites_id = t2.device_sites_id

      """).filter(x => {
      x.getLong(1) !=null
    })
  }


}
